﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;

namespace BuildForm
{
    public partial class Products : Form
    {
        SqlConnection con;
        SqlCommand cmd;
        public Products()
        {
            InitializeComponent();
            con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            InitData();
            LoadProductsInfo();
        }

        #region Code
        bool validateInput()
        {
            errorProvider1.Clear();
            bool error = false;
            try
            {
                #region Check Enter
                if (txtProductName.Text.Trim().Length == 0)
                {
                    errorProvider1.SetError(txtProductName, "Input Product Name!");
                    error = true;
                }

                if (cbSupplier.SelectedIndex < 0)
                {
                    errorProvider1.SetError(cbSupplier, "Choose Supplier!");
                    error = true;
                }

                if (cbCategory.SelectedIndex < 0)
                {
                    errorProvider1.SetError(cbCategory, "Choose Category!");
                    error = true;
                }

                if (txtUnitPrice.Text.Trim().Length == 0)
                {
                    errorProvider1.SetError(txtUnitPrice, "Input Unit Price!");
                    error = true;
                }
                else {
                    try
                    {
                        Decimal.Parse(txtUnitPrice.Text);
                    }
                    catch (Exception ex)
                    {
                        errorProvider1.SetError(txtUnitPrice, "Input Number!");
                        error = true;
                    }
                }

                if (rdTrue.Checked == false && rdFalse.Checked == false)
                {
                    errorProvider1.SetError(gbDiscontinued, "Choose Discontinued!");
                    error = true;
                }
                #endregion
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            if (error == true)
            {
                return false;
            }
            return true;
        }

        void LoadProductsInfo()
        {
            con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            //try
            //{
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "SELECT * FROM Production.Products";
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    gvProducts.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(),
                        dr[3].ToString(), dr[4].ToString(), dr[5].ToString());
                }
                dr.Close();
            //}
            //catch (Exception e)
            //{
            //    MessageBox.Show(e.Message);
            //}
            //finally
            //{
                con.Close();
            //}
        }

        void deleteProducts()
        {
            try
            {
                con.Open();
                cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "dbo.sp_Products_Delete";

                SqlParameter param = new SqlParameter("@productid", SqlDbType.Int);
                param.Value = txtProductID.Text;
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw(ex);
            }
            finally
            {
                con.Close();
                gvProducts.Rows.Clear();
                LoadProductsInfo();
            }
        }

        void updateProducts()
        {
            try
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_Products_Update";

                SqlParameter param = new SqlParameter("@productid", SqlDbType.Int);
                param.Value = txtProductID.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@productname", SqlDbType.NVarChar);
                param.Size = 40;
                param.Value = txtProductName.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@supplierid", SqlDbType.Int);
                param.Value = cbSupplier.SelectedValue;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@categoryid", SqlDbType.Int);
                param.Value = cbCategory.SelectedValue;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@unitprice", SqlDbType.Money);
                param.Value = txtUnitPrice.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@discontinued", SqlDbType.Bit);
                if (rdTrue.Checked)
                {
                    param.Value = true;
                }
                else
                {
                    param.Value = false;
                }
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();
                MessageBox.Show("Update Successfully!");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
                gvProducts.Rows.Clear();
                LoadProductsInfo();
            }
        }

        void addNewProducts()
        {
            try
            {
                con.Open();
                cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_Products_Insert";

                SqlParameter param = new SqlParameter("@productname", SqlDbType.NVarChar);
                param.Size = 40;
                param.Value = txtProductName.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@supplierid", SqlDbType.Int);
                param.Value = (Int32)cbSupplier.SelectedValue;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@categoryid", SqlDbType.Int);
                param.Value = (Int32)cbSupplier.SelectedValue;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@unitprice", SqlDbType.Money);
                param.Value = txtUnitPrice.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@discontinued", SqlDbType.Bit);
                if (rdTrue.Checked)
                {
                    param.Value = true;
                }
                else
                {
                    param.Value = false;
                }
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();
                MessageBox.Show("Add Successfully!");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
                gvProducts.Rows.Clear();
                LoadProductsInfo();
            }
        }

        void SearchProducts()
        {
            try
            {
                gvProducts.Rows.Clear();
                con.Open();
                cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.Text;
                if (cbSearch.SelectedIndex == 0)
                {
                    cmd.CommandText = string.Format("SELECT * FROM Production.Products WHERE productname LIKE '%{0}%'", txtSearch.Text);
                }
                else 
                {
                    if (cbSearch.SelectedIndex == 1)
                    {
                        cmd.CommandText = string.Format("SELECT * FROM Production.Products WHERE supplierid LIKE '%{0}%'", txtSearch.Text);
                    }
                    else
                    {
                        if (cbSearch.SelectedIndex == 2)
                        {
                            cmd.CommandText = string.Format("SELECT * FROM Production.Products WHERE categoryid LIKE '%{0}%'", txtSearch.Text);
                        }
                    }
                }
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    gvProducts.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(),
                        dr[3].ToString(), dr[4].ToString(), dr[5].ToString());
                }

                dr.Close();
            }
            catch (Exception)
            {
                MessageBox.Show("Please choose search by");
            }
            finally
            {
                con.Close();
            }
        }

        void InitData()
        {
            errorProvider1.Clear();
            btnAdd.Enabled = true;
            btnUpdate.Enabled = false;
            btnDelete.Enabled = false;
            txtProductID.Clear();
            txtProductName.Clear();
            cbSupplier.SelectedIndex = -1;
            cbCategory.SelectedIndex = -1;
            txtUnitPrice.Clear();
            rdTrue.Checked = false;
            rdFalse.Checked = false;
            txtSearch.Clear();
        }
        #endregion

        #region Event
        private void btnNew_Click(object sender, EventArgs e)
        {
            InitData();
            LoadProductsInfo();
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            errorProvider1.Clear();
            if (validateInput() == false)
            {
                return;
            }
            else
            {
                addNewProducts();
            }
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            if (validateInput() == true)
            {
                updateProducts();
            }
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            Boolean check = true;
            try
            {
                DialogResult rs = MessageBox.Show("Are You Sure?", "Warning", MessageBoxButtons.YesNo);
                if (rs == DialogResult.Yes)
                    deleteProducts();
                else
                    check = false;
            }
            catch
            {
                check = false;
                MessageBox.Show("Can not delete!", "Error");
            }
            if (check)
            {
                MessageBox.Show("Delete Successfully!");
                InitData();
            }
        }

        private void btnReload_Click(object sender, EventArgs e)
        {
            LoadProductsInfo();
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            SearchProducts();
        }

        private void gvProducts_SelectionChanged(object sender, EventArgs e)
        {
            errorProvider1.Clear();
            if (gvProducts.SelectedRows.Count > 0)
            {
                btnUpdate.Enabled = true;
                btnDelete.Enabled = true;
                btnAdd.Enabled = false;
                DataGridViewRow r = gvProducts.SelectedRows[0];
                txtProductID.Text = r.Cells["clProductID"].Value.ToString();
                txtProductName.Text = r.Cells["clProductName"].Value.ToString();
                cbSupplier.SelectedValue = r.Cells["clSupplier"].Value.ToString();       
                cbCategory.SelectedValue = r.Cells["clCategory"].Value.ToString();       
                txtUnitPrice.Text = r.Cells["clUnitPrice"].Value.ToString();
                if (r.Cells["clDiscontinued"].Value.ToString().Equals("True"))  
                {
                    rdTrue.Checked = true;
                }
                else
                    rdFalse.Checked = true;
            }
        }
        #endregion

        private void Products_Load(object sender, EventArgs e)
        {
            getDataSource_Supplier();
            getDataSource_Categories();
        }
        void getDataSource_Supplier()
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT supplierid, companyname FROM Production.Suppliers";
            da = new SqlDataAdapter(cmd);
            da.FillSchema(ds, SchemaType.Source);
            da.Fill(ds, "Production.Suppliers");
            DataTable dt = ds.Tables["Production.Suppliers"];
            cbSupplier.DataSource = dt;
            cbSupplier.DisplayMember = "companyname";
            cbSupplier.ValueMember = "supplierid";
        }
        void getDataSource_Categories()
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT categoryid, categoryname FROM Production.Categories";
            da = new SqlDataAdapter(cmd);
            da.FillSchema(ds, SchemaType.Source);
            da.Fill(ds, "Production.Categories");
            DataTable dt = ds.Tables["Production.Categories"];
            cbCategory.DataSource = dt;
            cbCategory.DisplayMember = "categoryname";
            cbCategory.ValueMember = "categoryid";
        }
    }
}
